package DAO;

import pojo.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class MessageDAO {
    public Connection conn=null;
    ResultSet rs=null;
    public static int pageSize = 3;
    public MessageDAO() {
        try {
            conn = BaseJdbc.getConnection();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }

    public List<Comment> LoadArticleComment(String articleID){
        try{
            String sql = "select * from tComment as a \n" +
                    "left join tUser b1 on a.ReplyUserID=b1.UserID \n" +
                    "left join tUser b2 on a.ByReplyUserID=b2.UserID where CommentParentID = 0 and ArticleID=" + articleID;
            PreparedStatement pr = conn.prepareStatement(sql);
            ResultSet resultSet = pr.executeQuery();
            List<Comment> commentList = new ArrayList<Comment>();
            while (resultSet.next()) {
                tComment tcomment = new tComment();
                Comment  comment= new Comment();
                tcomment.setCommentID(resultSet.getString(1));
                tcomment.setArticleID(resultSet.getString(2));
                tcomment.setCommentContent(resultSet.getString(3));
                tcomment.setReplyUserID(resultSet.getString(4));
                tcomment.setByReplyUserID(resultSet.getString(5));
                tcomment.setCommentCreateTime(resultSet.getString(6));
                tcomment.setCommentParentID(resultSet.getString(7));
                tcomment.setReplyUsername(resultSet.getString(9));
                tcomment.setByReplyUsername(resultSet.getString( 13));
                comment.setCommentMain(tcomment);
                commentList.add(comment);

            }
            for(int i = 0; i < commentList.size(); i ++){
                String commentID = commentList.get(i).getCommentMain().getCommentID();
                sql = "select * from tComment as a \n" +
                        "left join tUser b1 on a.ReplyUserID=b1.UserID \n" +
                        "left join tUser b2 on a.ByReplyUserID=b2.UserID where CommentParentID ="+ commentID+" and ArticleID=" + articleID;
                pr = conn.prepareStatement(sql);
                resultSet = pr.executeQuery();
                List<tComment> replyList = new ArrayList<tComment>();
                while (resultSet.next()) {
                    tComment tcomment = new tComment();
                    tcomment.setCommentID(resultSet.getString(1));
                    tcomment.setArticleID(resultSet.getString(2));
                    tcomment.setCommentContent(resultSet.getString(3));
                    tcomment.setReplyUserID(resultSet.getString(4));
                    tcomment.setByReplyUserID(resultSet.getString(5));
                    tcomment.setCommentCreateTime(resultSet.getString(6));
                    tcomment.setCommentParentID(resultSet.getString(7));
                    tcomment.setReplyUsername(resultSet.getString(9));
                    tcomment.setByReplyUsername(resultSet.getString(13));
                    replyList.add(tcomment);
                }
                commentList.get(i).setCommentItems(replyList);
            }
            return commentList;
        } catch (SQLException e){
            e.printStackTrace();
            return null;
        }
    };
    public List<ArticleItem> LoadArticle(){
        String sql="select * from tArticle inner join tUser on tUser.UserID=tArticle.UserID";
        PreparedStatement pr= null;
        try {
            pr = conn.prepareStatement(sql);
            ResultSet resultSet = pr.executeQuery();
            List<ArticleItem> list = new ArrayList<ArticleItem>();
            while(resultSet.next()){
                Article article=new Article();
                ArticleItem articleItem = new ArticleItem();
                article.setArticleID(resultSet.getString("ArticleID"));
                article.setUserID(resultSet.getString("UserID"));
                article.setArticleTitle(resultSet.getString("ArticleTitle"));
                article.setArticleContent(resultSet.getString("ArticleContent"));
                article.setArticleLike(resultSet.getString("ArticleLike"));
                article.setArticleFavorite(resultSet.getString("ArticleFavorite"));
                article.setArticleCreateTime(resultSet.getString("ArticleCreateTime"));
                article.setArticleView(resultSet.getString("ArticleView"));
                article.setArticleComment(resultSet.getString("ArticleComment"));

                articleItem.setArticle(article);
                articleItem.setUsername(resultSet.getString("Username"));
                list.add(articleItem);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    public boolean isExits(String userID, String articleID){
        PreparedStatement pr= null;
        try {
            String sql="select * from tArticle where ArticleID = ?";
            pr = conn.prepareStatement(sql);
            pr.setString(1,articleID);
            ResultSet resultSet = pr.executeQuery();
            while(resultSet.next()) {
                if (userID.equals(resultSet.getString("userID")) ) {
                    return true;
                } else {
                    return false;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return false;
    }
    public List<ArticleItem> ArticleSearch(String ArticleTitle){
        try{
            String sql="select * from tArticle inner join tUser on tUser.UserID=tArticle.UserID  where ArticleTitle like ?";
            PreparedStatement pr=conn.prepareStatement(sql);
            pr.setString(1,'%'+ArticleTitle+'%');
            ResultSet resultSet = pr.executeQuery();
            List<ArticleItem> list = new ArrayList<ArticleItem>();
            while(resultSet.next()){
                Article article=new Article();
                ArticleItem articleItem = new ArticleItem();
                article.setArticleID(resultSet.getString("ArticleID"));
                article.setUserID(resultSet.getString("UserID"));
                article.setArticleTitle(resultSet.getString("ArticleTitle"));
                article.setArticleContent(resultSet.getString("ArticleContent"));
                article.setArticleLike(resultSet.getString("ArticleLike"));
                article.setArticleFavorite(resultSet.getString("ArticleFavorite"));
                article.setArticleCreateTime(resultSet.getString("ArticleCreateTime"));
                article.setArticleView(resultSet.getString("ArticleView"));
                article.setArticleComment(resultSet.getString("ArticleComment"));
                articleItem.setArticle(article);
                articleItem.setUsername(resultSet.getString("Username"));
                list.add(articleItem);
            }
            return list;
        }catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    public List<ArticleItem> LoadArticle(String articleID){
        UpdateArticleFieldValue("ArticleView",articleID,"add");
        PreparedStatement pr= null;
        try {
            String sql="select * from tArticle inner join tUser on tUser.UserID=tArticle.UserID  where ArticleID = ?";
            pr = conn.prepareStatement(sql);
            pr.setString(1,articleID);
            ResultSet resultSet = pr.executeQuery();
            List<ArticleItem> list = new ArrayList<ArticleItem>();
            while(resultSet.next()){
                Article article=new Article();
                ArticleItem articleItem = new ArticleItem();
                article.setArticleID(resultSet.getString("ArticleID"));
                article.setUserID(resultSet.getString("UserID"));
                article.setArticleTitle(resultSet.getString("ArticleTitle"));
                article.setArticleContent(resultSet.getString("ArticleContent"));
                article.setArticleLike(resultSet.getString("ArticleLike"));
                article.setArticleFavorite(resultSet.getString("ArticleFavorite"));
                article.setArticleCreateTime(resultSet.getString("ArticleCreateTime"));
                article.setArticleView(resultSet.getString("ArticleView"));
                article.setArticleComment(resultSet.getString("ArticleComment"));
                articleItem.setArticle(article);
                articleItem.setUsername(resultSet.getString("Username"));
                list.add(articleItem);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    public boolean UpdateArticleFieldValue(String fieldName,String articleID,String operation){
        String sql = "";
        try {
            if (operation.equals("add")) {
                sql = "update tArticle set " + fieldName + "=" + fieldName + "+1 " + "where ArticleID=" + articleID;
            } else if (operation.equals("subtraction")){
                sql = "update tArticle set " + fieldName + "=" + fieldName + "-1 " + "where ArticleID=" + articleID;
            } else {
                return false;
            }
            PreparedStatement pr = conn.prepareStatement(sql);
            pr.executeUpdate();
            pr.close();
            return true;
        }catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
    public boolean writeArticleS(int userID ,String articleTitle,String articleContent){
        try{
            String sql = "insert into tArticle(UserID,ArticleTitle,ArticleContent, ArticleLike, ArticleFavorite,ArticleView,ArticleCreateTime,ArticleComment) values(?,?,?, ?,?,?,?,?)";
            String date = new SimpleDateFormat("yyyy-MM-dd").format(new Date()).toString();
            PreparedStatement pr=conn.prepareStatement(sql);
            pr.setInt(1,userID);
            pr.setString(2,articleTitle);
            pr.setString(3,articleContent);
            pr.setInt(4,0);
            pr.setInt(5,0);
            pr.setInt(6,0);
            pr.setString(7,date);
            pr.setInt(8,0);
            pr.executeUpdate();
            pr.close();
            return true;
        }catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
    public boolean uploadArticleComment(String articleID,String commentContent,String replyUserID,String byReplyUserID,String commentParentID){
        try{
            UpdateArticleFieldValue("ArticleComment",articleID,"add");
            String sql = "insert into tComment(ArticleID, CommentContent, ReplyUserID, ByReplyUserID, CommentCreateTime,CommentParentID)  values(?,?,?, ?,?,?)";
            String date = new SimpleDateFormat("yyyy-MM-dd").format(new Date()).toString();
            PreparedStatement pr=conn.prepareStatement(sql);
            pr.setString(1,articleID);
            pr.setString(2,commentContent);
            pr.setString(3,replyUserID);
            pr.setString(4,byReplyUserID);
            pr.setString(5,date);
            pr.setString(6,commentParentID);
            pr.executeUpdate();
            pr.close();
            return true;
        }catch (SQLException e) {
            e.printStackTrace();
            return false;
        }

    }


    public ArrayList<Article> getArticleList(int currentPageNo) throws SQLException {
        int BeginRecord;
        BeginRecord = (currentPageNo - 1) * pageSize; // 开始位置
        String sql="select * from tArticle limit " + BeginRecord + "," + pageSize;
        PreparedStatement pr=conn.prepareStatement(sql);
        rs=pr.executeQuery();
        ArrayList<Article> messageList=new ArrayList<Article>();
        try {
            while (rs.next()) {
                Article message= new Article();
                message.setArticleID(rs.getString(1));
                message.setUserID(rs.getString(2));
                message.setArticleTitle(rs.getString(3));
                message.setArticleContent(rs.getString(4));
                message.setArticleLike(rs.getString(5));
                message.setArticleFavorite(rs.getString(6));
                message.setArticleCreateTime(rs.getString(7));
                message.setArticleView(rs.getString(8));
                message.setArticleComment(rs.getString(9));
                messageList.add(message);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return messageList;
    }
    public int getPageCount2() throws SQLException {
        int total = 0; // 总记录数
        int PageCount = 0; // 页码总数
        String sql="select count(*) from tarticle";
        PreparedStatement pr=conn.prepareStatement(sql);
        rs=pr.executeQuery();
        try {
            if (rs.next()) {
                total = rs.getInt(1);
                PageCount = (total - 1) / pageSize + 1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return PageCount;
    }
    //总页码数
    public int geTotalPage2() throws SQLException {
        int total = 0; // 总记录数
        String sql="select count(*) from tarticle";
        PreparedStatement pr=conn.prepareStatement(sql);
        rs=pr.executeQuery();
        try {
            if (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }
    //总记录数
    public ArrayList<UserMessage> getMessageList(int currentPageNo) throws SQLException {
        int BeginRecord;
        BeginRecord = (currentPageNo - 1) * pageSize; // 开始位置
        String sql="select * from tuser limit " + BeginRecord + "," + pageSize;
        PreparedStatement pr=conn.prepareStatement(sql);
        rs=pr.executeQuery();
        ArrayList<UserMessage> messageList=new ArrayList<UserMessage>();
        try {
            while (rs.next()) {
                UserMessage message= new UserMessage();
                message.setUserID(rs.getInt(1));
                message.setUserName(rs.getString(2));
                message.setPassword(rs.getString(3));
                message.setState(rs.getInt(4));
                messageList.add(message);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return messageList;
    }
    // 分页统计
    public int getPageCount1() throws SQLException {
        int total = 0; // 总记录数
        int PageCount = 0; // 页码总数
        String sql="select count(*) from tuser";
        PreparedStatement pr=conn.prepareStatement(sql);
        rs=pr.executeQuery();
        try {
            if (rs.next()) {
                total = rs.getInt(1);
                PageCount = (total - 1) / pageSize + 1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return PageCount;
    }
    //总页码数
    public int geTotalPage1() throws SQLException {
        int total = 0; // 总记录数
        String sql="select count(*) from tuser";
        PreparedStatement pr=conn.prepareStatement(sql);
        rs=pr.executeQuery();
        try {
            if (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }
    //总记录数


    public ArrayList<UserMessage> allSearch(String UserName){
        try{
            String sql="select * from tuser where Username=?";
            PreparedStatement pr=conn.prepareStatement(sql);
            pr.setString(1,UserName);
            rs = pr.executeQuery();
            ArrayList<UserMessage> messageList=new ArrayList<UserMessage>();
            while(rs.next()){
                UserMessage message=new UserMessage();
                message.setUserID(rs.getInt(1));
                message.setUserName(rs.getString(2));
                message.setPassword(rs.getString(3));
                message.setState(rs.getInt(4));
                messageList.add(message);
            }
            return messageList;
        }catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    public ArrayList<UserMessage> adminLoginServlet(String UserName){
        try{
            String sql="select Username,Password from tAdminUser where Username = ? ";
            PreparedStatement pr=conn.prepareStatement(sql);
            pr.setString(1,UserName);
            rs = pr.executeQuery();
            UserMessage Message=new UserMessage();
            ArrayList<UserMessage> messageList=new ArrayList<UserMessage>();
            while(rs.next()){
                Message.setUserName(rs.getString(1));
                Message.setPassword(rs.getString(2));

                messageList.add(Message);
            }
            return messageList;
        }catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public ArrayList<UserMessage> loginServlet(String UserName){
        try{
            String sql="select UserID,Username,Password,state from tUser where Username = ? ";
            PreparedStatement pr=conn.prepareStatement(sql);
            pr.setString(1,UserName);
            rs = pr.executeQuery();
            UserMessage userMessage=new UserMessage();
            ArrayList<UserMessage> messageList=new ArrayList<UserMessage>();
            while(rs.next()){
                userMessage.setUserID(rs.getInt(1));
                userMessage.setUserName(rs.getString(2));
                userMessage.setPassword(rs.getString(3));
                userMessage.setState(rs.getInt(4));
                messageList.add(userMessage);
            }
            return messageList;
        }catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public UserMessage judgeServlet1(String UserName){
        try{
            String sql="select Username from tUser where Username = ? ";
            PreparedStatement pr=conn.prepareStatement(sql);
            pr.setString(1,UserName);
            rs = pr.executeQuery();
            UserMessage userMessage=new UserMessage();
            while(rs.next()){
                userMessage.setUserName(rs.getString(1));
            }
            return userMessage;
        }catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    public UserMessage judgeServlet2(String UserName){
        try{
            String sql="select Username from tAdminUser where Username = ? ";
            PreparedStatement pr=conn.prepareStatement(sql);
            pr.setString(1,UserName);
            rs = pr.executeQuery();
            UserMessage userMessage=new UserMessage();
            while(rs.next()){
                userMessage.setUserName(rs.getString(1));
            }
            return userMessage;
        }catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    public boolean enrollServlet(String UserName, String password){
        if(judgeServlet1(UserName).getUserName()==null){
            try{
                String sql="insert into tUser(Username,Password) values(?,?)";
                PreparedStatement pr=conn.prepareStatement(sql);
                pr.setString(1,UserName);
                pr.setString(2,password);
                pr.executeUpdate();
                pr.close();
                return true;
            }catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
        }
        else
            return false;
    }
    public boolean ChangeServlet(String NowName,int state){
        try{
            String sql="update tUser set state=? where Username=?";
            PreparedStatement pr=conn.prepareStatement(sql);
            pr.setInt(1,state);
            pr.setString(2,NowName);
            pr.executeUpdate();
            pr.close();
            return true;
        }catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
    public boolean delArticle(String ArticleID){
        try{
            String sql="delete from tArticle where ArticleID=?";
            PreparedStatement pr=conn.prepareStatement(sql);

            pr.setString(1,ArticleID);
            pr.executeUpdate();
            pr.close();
            return true;
        }catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
}
